/*
 * Created on Jul 2, 2004
 *
 * To change the template for this generated file go to
 * Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments
 */
package com.lite;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
 * @author Isabelle
 *
 * To change the template for this generated type comment go to
 * Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments
 */
public class OracleDialect implements DatabaseDialect
{
    public static final String QUERYPLANSQL = "SELECT ' '||level||'      ' lev, lpad(operation, length(operation) + 4*(level-1)) || decode(id, 0, '   Optimizer='||optimizer, null) ||'  '||options||\n" 
        									+ "decode(object_name,null,null,'  OF  ')||object_name||\n" + "decode(object_type,'UNIQUE', ' (U) ', 'NON-UNIQUE',\n" + "'(NU)',null) plan \n" 
        									+ "FROM   PLAN_TABLE \n" + "START with ID = 0 and STATEMENT_ID = 'pk00001' \n" + "CONNECT by prior ID = PARENT_ID and STATEMENT_ID = 'pk00001' \n";
    
    public static final String DELETEPLANSQL = "DELETE FROM PLAN_TABLE";
	/* (non-Javadoc)
	 * @see com.pk.DatabaseDialect#getKeywords()
	 */
	public Vector<String> getKeywords()
	{
		Vector<String> keywords = new Vector<String>();
		keywords.addElement("select");
		keywords.addElement("set");
		keywords.addElement("or");
		keywords.addElement("and");
		keywords.addElement("where");
		keywords.addElement("from");
		keywords.addElement("insert");
		keywords.addElement("update");
		keywords.addElement("order");
		keywords.addElement("by");
		keywords.addElement("close");
		keywords.addElement("commit");
		keywords.addElement("rollback");
		keywords.addElement("create");
		keywords.addElement("database");
		keywords.addElement("drop");
		keywords.addElement("table");
		keywords.addElement("view");
		keywords.addElement("join");
		keywords.addElement("into");
		keywords.addElement("between");
		keywords.addElement("values");
		keywords.addElement("alter");
		keywords.addElement("add");
		keywords.addElement("modify");
		keywords.addElement("close");
		keywords.addElement("distinct");
		keywords.addElement("index");
		keywords.addElement("grant");
		keywords.addElement("delete");
		keywords.addElement("group");
		keywords.addElement("as");
		keywords.addElement("having");
		keywords.addElement("delete");
		keywords.addElement("like");
		keywords.addElement("unique");
		keywords.addElement("primary");
		keywords.addElement("key");
		keywords.addElement("not");
		keywords.addElement("null");
		keywords.addElement("asc");
		keywords.addElement("desc");
		keywords.addElement("revoke");
		keywords.addElement("remark");
		keywords.addElement("exists");
		keywords.addElement("union");
		keywords.addElement("decode");


		return keywords;
	}

	/* (non-Javadoc)
	 * @see com.pk.DatabaseDialect#getTableInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
	 */
	public String getTableInfoSQLString(String argTableName, ConnectionInformation argConnectionInformation)
	{
		String sqlString = "SELECT COLUMN_NAME, DECODE(NULLABLE,'N', 'NOT NULL', 'Y', NULL)  AS NULLABLE, " + "DATA_TYPE, DECODE(DATA_TYPE, 'VARCHAR2', TO_CHAR(DATA_LENGTH), 'NUMBER', " + "DECODE(DATA_SCALE,0,TO_CHAR(DATA_PRECISION),NULL,NULL,DATA_PRECISION||','||DATA_SCALE)) AS \"SIZE\" " + "FROM USER_TAB_COLUMNS " + "WHERE TABLE_NAME = '" + argTableName + "'" + " ORDER BY COLUMN_ID";
		return sqlString;
	}

	/* (non-Javadoc)
	 * @see com.pk.DatabaseDialect#getIndexInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
	 */
	public String getIndexInfoSQLString(String argTableName, ConnectionInformation argConnectionInformation)
	{
		String sqlString = "SELECT TABLE_NAME,INDEX_NAME, " + "COLUMN_POSITION AS ID,COLUMN_NAME " + "FROM USER_IND_COLUMNS " + "WHERE   TABLE_NAME  LIKE UPPER('" + argTableName + "')||'%' " + "ORDER BY TABLE_NAME,INDEX_NAME,COLUMN_POSITION";
		return sqlString;
	}

	/* (non-Javadoc)
	 * @see com.pk.DatabaseDialect#getProcedureInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
	 */
	public String getProcedureInfoSQLString(String argProcedureName, ConnectionInformation argConnectionInformation)
	{
		String sqlString = "SELECT DECODE(UPPER(TRIM(TEXT)),'END;',TEXT,SUBSTR(TEXT,1,LENGTH(TEXT)-1)) AS \"PROCEDURE SOURCE\" FROM USER_SOURCE " + "WHERE TYPE='PROCEDURE' " + "AND   NAME='" + argProcedureName + "'" + "ORDER BY LINE";
		return sqlString;
	}

	/* (non-Javadoc)
	 * @see com.pk.DatabaseDialect#isSelectStatement(java.lang.String)
	 */
	public boolean isSelectStatement(String argStatement)
	{
		boolean isSelect = false;
		if(argStatement.toUpperCase().startsWith("SELECT") || argStatement.toUpperCase().startsWith("SHOW") )
		{
			isSelect = true;
		}
		return isSelect;
	}

	/* (non-Javadoc)
	 * @see com.pk.DatabaseDialect#isDescribeStatement(java.lang.String)
	 */
	public boolean isDescribeStatement(String argStatement)
	{
		boolean isDescribe = false;
		if(argStatement.toUpperCase().startsWith("DESC"))
		{
			isDescribe = true;
		}
		return isDescribe;
	}

	/* (non-Javadoc)
	 * @see com.pk.DatabaseDialect#getDescribeSQLString(java.lang.String, com.pk.ConnectionInformation)
	 */
	public String getDescribeSQLString(String argQuery, ConnectionInformation argConnectionInformation)
	{
		String tName = argQuery.substring(5);
		String describeSQLString = "SELECT COLUMN_NAME, DECODE(NULLABLE,'N', 'NOT NULL', 'Y', NULL)  AS NULLABLE, " + "DATA_TYPE, DECODE(DATA_TYPE, 'VARCHAR2', TO_CHAR(DATA_LENGTH), 'NUMBER', " + "DECODE(DATA_SCALE,0,TO_CHAR(DATA_PRECISION),NULL,NULL,DATA_PRECISION||','||DATA_SCALE)) AS \"SIZE\" " + "FROM ALL_TAB_COLUMNS " + "WHERE TABLE_NAME = '" + tName.toUpperCase() + "'" + " ORDER BY COLUMN_ID"; 
		return describeSQLString;
	}

	/* (non-Javadoc)
	 * @see com.pk.DatabaseDialect#doExecutePlan(java.lang.String, com.pk.ConnectionInformation, java.sql.Connection)
	 */
	public String doExecutePlan(String argStatement, ConnectionInformation argConnectionInformation, Connection argConnection) throws SQLException
	{
		String query = "EXPLAIN PLAN SET STATEMENT_ID = 'pk00001' FOR " + argStatement;
		String planOut = "";
		Statement statement = argConnection.createStatement();
		statement.executeQuery(query);

		ResultSet planResult = statement.executeQuery(QUERYPLANSQL);

		while (planResult.next())
		{
				planOut = planOut + //planResult.getString("id") + 
	//planResult.getString("p_id") + 
				planResult.getString("lev") + planResult.getString("plan") + "\n";
		}


		statement.executeUpdate(DELETEPLANSQL);

		
		return planOut;
	}

    /* (non-Javadoc)
     * @see com.pk.DatabaseDialect#getPackageInfoSQLString(java.lang.String, com.pk.ConnectionInformation)
     */
    public String getPackageInfoSQLString(String argProcedureName, ConnectionInformation argConnectionInformation)
    {
        return "SELECT DECODE(UPPER(TRIM(TEXT)),'END;',TEXT,SUBSTR(TEXT,1,LENGTH(TEXT)-1)) AS \"PACKAGE SOURCE\" FROM USER_SOURCE " + "WHERE TYPE='PACKAGE' " + "AND   NAME='" + argProcedureName + "'" + "ORDER BY LINE";
    }

}
